# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html


# useful for handling different item types with a single interface
import psycopg2

class XueyuanSpiderPipeline:
    def __init__(self):
        self.connection = psycopg2.connect(host='localhost', user='ASUS', password='nicaine2233', database='postgres',
                                           port='5432')
        self.connection.autocommit = True

    def open_spider(self, spider):
        self.cursor = self.connection.cursor()

    def close_spider(self, spider):
        self.cursor.close()
        self.connection.close()

    def process_item(self, item, spider):
        # 重复不爬取
        query = "SELECT * from article WHERE article_url ='%s'" % item['url']
        self.cursor.execute(query)
        result = self.cursor.fetchone()
        if result == None:
            sql = '''INSERT INTO article
                (date, content, pic_link, article_url, title, source_name)
                VALUES(%s, %s, %s, %s, %s, %s);'''
            self.cursor.execute(sql, (
                item['date'],
                item['content'],
                item['pic'],
                item['url'],
                item['title'],
                item['source']
            ))
            self.connection.commit()
        else:
            pass


        return item
